package com.testingedu.jdbcDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.model.TCategory;

/**
 * 表 TCategory 分类增删改查 修订时间:2012-11-19 修订人:bfc 版本:01
 * 
 * @author Administrator
 */
public class TCategoryDao extends BaseDao<TCategory> {

	public TCategoryDao() {
		clazz = TCategory.class;
	}

	/**
	 * 增加分类
	 * 
	 * @param tbTrainInfo
	 * @return
	 */
	public int add(TCategory tCategory) throws Exception {
		String sql = "insert into t_category (name,super_id,description) value(?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(tCategory.getName());
		values.add(tCategory.getSuperId());
		values.add(tCategory.getDescription());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return getMaxId();
	}

	private int getMaxId() throws Exception {
		String sql = "select max(id) as _id from t_category";
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql, null);
			if (rs.next()) {
				return rs.getInt("_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return 0;
	}

	/**
	 * 更新分类
	 * 
	 * @param tbTrainInfo
	 * @return
	 */
	public void update(TCategory tCategory) throws Exception {
		String sql = "update t_category set name=?,super_id=?,description=? where id=?";
		List<Object> values = new ArrayList<Object>();
		values.add(tCategory.getName());
		values.add(tCategory.getSuperId());
		values.add(tCategory.getDescription());
		values.add(tCategory.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	/**
	 * 删除分类
	 * 
	 * @param id
	 * @return
	 */
	public void delete(int id) throws Exception {
		String sql = "delete from t_category  where id=?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	/**
	 * 列表显示类别 最后修改时间:20102-11-22 修改人：bfc
	 * 
	 * @return
	 */
	public void list(QueryResult queryResult, TCategory tCategory)
			throws Exception {
		StringBuffer sql = new StringBuffer(
				"select * from t_category tp where 1=1");
		List<Object> values = new ArrayList<Object>();
		if (tCategory != null) {
			if (tCategory.getName() != null
					&& tCategory.getName().trim().length() > 0) {
				sql.append(" and tp.name like ?");
				values.add("%" + tCategory.getName() + "%");
			}
		}
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			rs.next();
			queryResult.setAllCount(new Long(rs.getInt(1)));
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TCategory info(int id) throws Exception {
		String sql = "select * from t_category tp where tp.id = ?";
		List<TCategory> result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	public List<TCategory> _resultSetToList(ResultSet rs) throws Exception {
		List<TCategory> result = new ArrayList<TCategory>();
		try {
			while (rs.next()) {
				TCategory tCategory = new TCategory();
				tCategory.setId(rs.getInt("id"));
				tCategory.setDescription(rs.getString("description"));
				tCategory.setName(rs.getString("name"));
				Integer superId = rs.getInt("super_id");
				tCategory.setSuperId(superId);
				if (superId == 0) {
					tCategory.setSuperName("无");
				} else {
					TCategory c = this.info(superId);
					tCategory.setSuperName(c.getName());
				}
				result.add(tCategory);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}
}
